
package a.student.helper;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.app.Activity;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public final class DatabaseHelper extends SQLiteOpenHelper {
	
    private static final int DATABASE_VERSION = 1;
    private static String DATABASE_PATH = "/data/data/vn.student/databases/";
    public static final String DATABASE_EXTENSION = ".db3";
    public static final String DATABASE_NAME = "vn_db" + DATABASE_EXTENSION;
    public static final String TEMP_PREFIX = "tmp_db_";
    
    private SQLiteDatabase myDB;
    private final Context context;
    
    private Activity act;
    
    public static DatabaseHelper getInstance(Context context, String databaseName) {
        return new DatabaseHelper(context, databaseName);
    }
    
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
    }
    
    private DatabaseHelper(Context context, String databaseName) {
        super(context, databaseName, null, DATABASE_VERSION);
        this.context = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    	boolean dbExist  = checkDataBase();
		if(dbExist){
			// do nothing database already exist
		}else {
			db.execSQL("" +
					"CREATE TABLE \"Assignment\" (" +
					"\"A_ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ," +
					"\"Priority\" INTEGER," +
					"\"Due_Date\" DATETIME NOT NULL  DEFAULT CURRENT_DATE," +
					"\"Description\" TEXT," +
					"\"Alert_Time\" DATETIME DEFAULT CURRENT_TIME," +
					"\"CL_ID\" INTEGER NOT NULL references Class(CL_ID), \"Done\" BOOL not null default 0, \"Name\"  VARCHAR)" +
					"");
			db.execSQL("" +
					"CREATE TABLE \"Cancelled_Class\" ("+
					"\"CC_ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,"+ 
					"\"Date\" DATETIME NOT NULL  DEFAULT CURRENT_DATE,"+
					"\"CL_ID\" INTEGER NOT NULL  references Class(CL_ID))" +
					"");
			db.execSQL("" +
					"CREATE TABLE \"Class\" (\"CL_ID\" INTEGER PRIMARY KEY  NOT NULL ,\"C_ID\"" +
					" INTEGER NOT NULL ,\"Class_Name\" VARCHAR NOT NULL ,\"Type\" VARCHAR NOT NULL ," +
					"\"Location\" VARCHAR,\"Date_Start\" DATETIME DEFAULT CURRENT_DATE ,\"Date_End\" " +
					" DATETIME DEFAULT CURRENT_DATE ,\"Time_Start\" DATETIME DEFAULT CURRENT_TIME ," +
					"\"Time_End\" DATETIME DEFAULT CURRENT_TIME ,\"Day_repeat\" VARCHAR,\"CT_ID\" INTEGER, " +
					"\"Repeat\" INTEGER)" +
					"");
			db.execSQL("" +
					"CREATE TABLE \"Contact\" ("+
					"\"CT_ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,"+ 
					"\"CT_Name\" VARCHAR NOT NULL UNIQUE ,"+ 
					"\"Email\" VARCHAR,"+ 
					"\"PhoneNo\" VARCHAR,"+ 
					"\"Type\" VARCHAR,"+ 
					"\"Website\" VARCHAR,"+ 
					"\"Note\" TEXT,"+ 
					"\"Address\" VARCHAR," + 
					"\"Photo\" VARCHAR)" +
					"");
			db.execSQL("" +
					"CREATE TABLE \"Contact_Assignment\"("+ 
					"\"A_ID\" INTEGER NOT NULL references Assignment(A_ID) ," + 
					"\"CT_ID\" INTEGER NOT NULL references Contact(CT_ID),"+
					"PRIMARY KEY (\"CT_ID\", \"A_ID\"))" +
					"");
			db.execSQL("" +
					"CREATE TABLE \"Course\" (\"C_ID\" INTEGER PRIMARY KEY " +
					" AUTOINCREMENT  NOT NULL , \"S_ID\" INTEGER NOT NULL REFERENCES " +
					" Semester(S_ID) , \"Date_Start\" DATETIME DEFAULT CURRENT_DATE, " +
					"\"Date_End\" DATETIME DEFAULT CURRENT_DATE, \"C_Name\" VARCHAR NOT NULL )" +
					"");
			db.execSQL("" +
					"CREATE TABLE \"Exam\" ("+
					" \"E_ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,"+ 
					" \"Type\" VARCHAR NOT NULL ,"+
					" \"Date\" DATETIME NOT NULL DEFAULT CURRENT_DATE , "+ 
					" \"Time\" INTEGER, "+
					" \"Location\" VARCHAR, "+ 
					" \"CL_ID\" INTEGER REFERENCES Class(CL_ID), "+ 
					" \"C_ID\" INTEGER NOT NULL REFERENCES Course(C_ID) )" +
					"");
			db.execSQL("" +
					"CREATE TABLE \"Holiday\" ( "+
					" \"H_ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "+ 
					" \"H_Name\" VARCHAR NOT NULL DEFAULT CURRENT_DATE, "+ 
					" \"Date\" DATETIME NOT NULL )" +
					"");
			db.execSQL("" +
					"CREATE TABLE \"Semester\" (\"S_ID\" INTEGER PRIMARY KEY" +
					"  AUTOINCREMENT  NOT NULL , \"S_Name\" VARCHAR NOT NULL , " +
					" \"Date_Start\" DATETIME DEFAULT CURRENT_DATE, \"Date_End\" " +
					"DATETIME DEFAULT CURRENT_DATE)" +
					"");
			db.execSQL("" +
					"CREATE VIEW \"V_Course\" AS select c.C_ID, cl.CL_ID, a.A_ID"+
					" from Course c, Class cl, Assignment a, Contact_Assignment ca"+
					" where c.C_ID=cl.C_ID"+
					" and cl.CL_ID=a.CL_ID"+
					" and a.A_ID=ca.A_ID" +
					"");
			db.execSQL("" +
					"CREATE VIEW \"V_Semester\" AS select s.S_ID, c.C_ID, cl.CL_ID, a.A_ID"+
					" from Semester s, Course c, Class cl, Assignment a, Contact_Assignment ca"+
					" where s.S_ID=c.S_ID "+
					" and c.C_ID=cl.C_ID "+
					" and cl.CL_ID=a.CL_ID "+
					" and a.A_ID=ca.A_ID" +
					"");
			db.execSQL("" +
					"CREATE TRIGGER _fk_Assignment_delete "+
					" after delete on \"Assignment\" for each row "+
					" begin "+
					" delete from \"Contact_Assignment\" where \"A_ID\"=old.\"A_ID\"; "+
					" end" +
					"");
			db.execSQL("" +
					"CREATE TRIGGER _fk_Assignment_insert_0 BEFORE INSERT" +
					" ON \"Assignment\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
					" 'insert on table Assignment violates foreign key constraint') " +
					" WHERE  (SELECT \"CL_ID\" FROM \"Class\" WHERE \"CL_ID\" = NEW.\"CL_ID\") IS NULL; END" +
					"");
			db.execSQL("" +
					"CREATE TRIGGER _fk_Assignment_update_0 BEFORE UPDATE ON \"Assignment\" " +
					" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table Assignment violates" +
					" foreign key constraint') WHERE  (SELECT \"CL_ID\" FROM \"Class\" WHERE \"CL_ID\" =" +
					"  NEW.\"CL_ID\") IS NULL; END" +
					"");
			db.execSQL("" +
					"CREATE TRIGGER _fk_Contact_Assignment_insert_0 BEFORE INSERT ON " +
					" \"Contact_Assignment\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
					" 'insert on table Contact_Assignment violates foreign key constraint')" +
					"  WHERE  (SELECT \"CT_ID\" FROM \"Contact\" WHERE \"CT_ID\" = NEW.\"CT_ID\") IS NULL; END" +
					"");
			
			db.execSQL("" +
					"CREATE TRIGGER _fk_Contact_Assignment_insert_1 BEFORE INSERT ON" +
					" \"Contact_Assignment\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
					"'insert on table Contact_Assignment violates foreign key constraint') " +
					"WHERE  (SELECT \"A_ID\" FROM \"Assignment\" WHERE \"A_ID\" = NEW.\"A_ID\") IS NULL; END" +
					"");
			db.execSQL("" +
					"CREATE TRIGGER _fk_Contact_Assignment_update_0 BEFORE UPDATE ON " +
					" \"Contact_Assignment\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
					" 'update on table Contact_Assignment violates foreign key constraint') " +
					" WHERE  (SELECT \"CT_ID\" FROM \"Contact\" WHERE \"CT_ID\" = NEW.\"CT_ID\") IS NULL; END" +
					"");
			db.execSQL("" +
					"CREATE TRIGGER _fk_Contact_Assignment_update_1 BEFORE UPDATE ON" +
					"  \"Contact_Assignment\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
					" 'update on table Contact_Assignment violates foreign key constraint') " +
					" WHERE  (SELECT \"A_ID\" FROM \"Assignment\" WHERE \"A_ID\" = NEW.\"A_ID\") IS NULL; END" +
					"");
			db.execSQL("" +
					"CREATE TRIGGER _fk_Contact_delete"+
					" after delete on \"Contact\" for each row "+
					" begin "+
					" delete from \"Contact_Assignment\" where \"CT_ID\"=old.\"CT_ID\";"+
					" update \"Class\" set \"CT_ID\"=NULL where \"CT_ID\"=old.\"CT_ID\";  "+
					" end" +
					"");
			db.execSQL("" +
					"CREATE TRIGGER _fk_Course_delete"+
					" after delete on \"Course\" for each row"+
					" begin"+
					" delete from \"Contact_Assignment\" where \"A_ID\" in "+
					" (select \"A_ID\" from V_Course where \"C_ID\"=old.\"C_ID\");"+
					" delete from \"Assignment\" where \"CL_ID\" in (select CL_ID from \"Class\" where \"C_ID\"=old.\"C_ID\");"+
					" delete from \"Class\" where \"C_ID\"=old.\"C_ID\";"+
					" delete from \"Exam\" where \"C_ID\"=old.\"C_ID\";"+
					" end" +
					"");
			db.execSQL("" +
					"CREATE TRIGGER _fk_Semester_delete"+
					" after delete on \"Semester\" for each row"+
					" begin"+
					" delete from \"Contact_Assignment\" where \"A_ID\" in "+
					" (select \"A_ID\" from V_Semester where \"S_ID\"=old.\"S_ID\");"+
					" delete from \"Assignment\" where \"CL_ID\" in "+
					" (select \"CL_ID\" from V_Semester where \"S_ID\"=old.\"S_ID\");"+
					" delete from \"Class\" where \"C_ID\" in  (select \"C_ID\" from V_Semester where \"S_ID\"=old.\"S_ID\"); "+
					" delete from \"Exam\" where \"C_ID\" in  (select \"C_ID\" from V_Semester where \"S_ID\"=old.\"S_ID\"); "+
					" delete from \"Course\" where \"S_ID\"=old.\"S_ID\"; "+
					"end" +
					"");
			db.execSQL("" +
					"CREATE TRIGGER _fk_Course_insert_0 BEFORE INSERT ON " +
					" \"Course\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
					" 'insert on table Course violates foreign key constraint')" +
					" WHERE  (SELECT \"S_ID\" FROM \"Semester\" WHERE \"S_ID\" = NEW.\"S_ID\") IS NULL; END" +
					"");
			db.execSQL("" +
					"CREATE TRIGGER _fk_Course_update_0 BEFORE UPDATE ON" +
					" \"Course\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK," +
					" 'update on table Course violates foreign key constraint') WHERE " +
					" (SELECT \"S_ID\" FROM \"Semester\" WHERE \"S_ID\" = NEW.\"S_ID\") IS NULL; END" +
					"");
			db.execSQL("" +
					"CREATE TRIGGER _fk_Exam_insert_0 BEFORE INSERT ON \"Exam\" " +
					" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK," +
					" 'insert on table Exam violates foreign key constraint') WHERE " +
					" (SELECT \"C_ID\" FROM \"Course\" WHERE \"C_ID\" = NEW.\"C_ID\") IS NULL; END" +
					"");
			db.execSQL("" +
					"CREATE TRIGGER _fk_Exam_insert_1 BEFORE INSERT ON" +
					" \"Exam\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK," +
					" 'insert on table Exam violates foreign key constraint')" +
					" WHERE  NEW.\"CL_ID\" IS NOT NULL AND  (SELECT \"CL_ID\" FROM \"Class\"" +
					" WHERE \"CL_ID\" = NEW.\"CL_ID\") IS NULL; END" +
					"");
			db.execSQL("" +
					"CREATE TRIGGER _fk_Exam_update_0 BEFORE UPDATE ON" +
					" \"Exam\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
					" 'update on table Exam violates foreign key constraint') WHERE " +
					" (SELECT \"C_ID\" FROM \"Course\" WHERE \"C_ID\" = NEW.\"C_ID\") IS NULL; END" +
					"");
			db.execSQL("" +
					"CREATE TRIGGER _fk_Exam_update_1 BEFORE UPDATE ON \"Exam\" " +
					" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
					"'update on table Exam violates foreign key constraint')" +
					" WHERE  NEW.\"CL_ID\" IS NOT NULL AND  (SELECT \"CL_ID\" FROM \"Class\" " +
					" WHERE \"CL_ID\" = NEW.\"CL_ID\") IS NULL; END" +
					"");
		}
		
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
	@Override
	public synchronized void close(){
		if(myDB != null)
			myDB.close();
		super.close();
	}
	
	/*****************************************************/

	public void copyDataBase() throws IOException{
		//Open your local db as the input stream
		InputStream myInput = context.getAssets().open(DATABASE_NAME);
		String outFileName = DATABASE_PATH+DATABASE_NAME;
		OutputStream myOutput = new FileOutputStream(outFileName);
		byte[] buffer = new byte[1024];
		int leng;
		while((leng = myInput.read(buffer))>0){
			myOutput.write(buffer, 0, leng);
		}
		myOutput.flush();
		myOutput.close();
		myInput.close();
	}

	public void createDataBase() throws IOException{
		boolean dbExist  = checkDataBase();
		if(dbExist){
			// do nothing database already exist
		}else {
			this.getReadableDatabase();
			createAllTable();
		}
		
	}

	public void dropDatabase() throws IOException{
		boolean dbExist  = checkDataBase();
		if(dbExist){
			this.getReadableDatabase();
			try {
				//
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error drop database");
			}
		}else {
			// do nothing database already exist
		}
		
	}

	public void execSQL(String sql){
		this.openDatabase();
		myDB.execSQL(sql);
		this.close();
	}

	public SQLiteDatabase getDB(){
		return myDB;
	}

	public SQLiteDatabase openDatabase() throws SQLException{
		String myPath = DATABASE_PATH + DATABASE_NAME;
		if(!this.checkDataBase())
			try {
				createDataBase();
			} catch (IOException e) {
				
			}
		myDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
		return myDB;
	}

	public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy){
		this.openDatabase();
		Cursor result = myDB.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
		return result;
	}

	private boolean checkDataBase() {
		SQLiteDatabase checkDB= null;
		try {
			String myPath = DATABASE_PATH + DATABASE_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
		} catch (SQLiteException e) {
			// database doesn't exist yet
		}
		if(checkDB!=null){
			checkDB.close();
		}
		return checkDB!=null? true:false;
	}

	private void createAllTable(){
		this.openDatabase();
		this.execSQL("" +
				"CREATE TABLE \"Assignment\" (" +
				"\"A_ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ," +
				"\"Priority\" INTEGER," +
				"\"Due_Date\" DATETIME NOT NULL  DEFAULT CURRENT_DATE," +
				"\"Description\" TEXT," +
				"\"Alert_Time\" DATETIME DEFAULT CURRENT_TIME," +
				"\"CL_ID\" INTEGER NOT NULL references Class(CL_ID), \"Done\" BOOL not null default 0, \"Name\"  VARCHAR)" +
				"");
		this.execSQL("" +
				"CREATE TABLE \"Cancelled_Class\" ("+
				"\"CC_ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,"+ 
				"\"Date\" DATETIME NOT NULL  DEFAULT CURRENT_DATE,"+
				"\"CL_ID\" INTEGER NOT NULL  references Class(CL_ID))" +
				"");
		this.execSQL("" +
				"CREATE TABLE \"Class\" (\"CL_ID\" INTEGER PRIMARY KEY  NOT NULL ,\"C_ID\"" +
				" INTEGER NOT NULL ,\"Class_Name\" VARCHAR NOT NULL ,\"Type\" VARCHAR NOT NULL ," +
				"\"Location\" VARCHAR,\"Date_Start\" DATETIME DEFAULT CURRENT_DATE ,\"Date_End\" " +
				" DATETIME DEFAULT CURRENT_DATE ,\"Time_Start\" DATETIME DEFAULT CURRENT_TIME ," +
				"\"Time_End\" DATETIME DEFAULT CURRENT_TIME ,\"Day_repeat\" VARCHAR,\"CT_ID\" INTEGER, " +
				"\"Repeat\" INTEGER)" +
				"");
		this.execSQL("" +
				"CREATE TABLE \"Contact\" ("+
				"\"CT_ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,"+ 
				"\"CT_Name\" VARCHAR NOT NULL UNIQUE ,"+ 
				"\"Email\" VARCHAR,"+ 
				"\"PhoneNo\" VARCHAR,"+ 
				"\"Type\" VARCHAR,"+ 
				"\"Website\" VARCHAR,"+ 
				"\"Note\" TEXT,"+ 
				"\"Address\" VARCHAR," + 
				"\"Photo\" VARCHAR)" +
				"");
		this.execSQL("" +
				"CREATE TABLE \"Contact_Assignment\"("+ 
				"\"A_ID\" INTEGER NOT NULL references Assignment(A_ID) ," + 
				"\"CT_ID\" INTEGER NOT NULL references Contact(CT_ID),"+
				"PRIMARY KEY (\"CT_ID\", \"A_ID\"))" +
				"");
		this.execSQL("" +
				"CREATE TABLE \"Course\" (\"C_ID\" INTEGER PRIMARY KEY " +
				" AUTOINCREMENT  NOT NULL , \"S_ID\" INTEGER NOT NULL REFERENCES " +
				" Semester(S_ID) , \"Date_Start\" DATETIME DEFAULT CURRENT_DATE, " +
				"\"Date_End\" DATETIME DEFAULT CURRENT_DATE, \"C_Name\" VARCHAR NOT NULL )" +
				"");
		this.execSQL("" +
				"CREATE TABLE \"Exam\" ("+
				" \"E_ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,"+ 
				" \"Type\" VARCHAR NOT NULL ,"+
				" \"Date\" DATETIME NOT NULL DEFAULT CURRENT_DATE , "+ 
				" \"Time\" INTEGER, "+
				" \"Location\" VARCHAR, "+ 
				" \"CL_ID\" INTEGER REFERENCES Class(CL_ID), "+ 
				" \"C_ID\" INTEGER NOT NULL REFERENCES Course(C_ID) )" +
				"");
		this.execSQL("" +
				"CREATE TABLE \"Holiday\" ( "+
				" \"H_ID\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "+ 
				" \"H_Name\" VARCHAR NOT NULL DEFAULT CURRENT_DATE, "+ 
				" \"Date\" DATETIME NOT NULL )" +
				"");
		this.execSQL("" +
				"CREATE TABLE \"Semester\" (\"S_ID\" INTEGER PRIMARY KEY" +
				"  AUTOINCREMENT  NOT NULL , \"S_Name\" VARCHAR NOT NULL , " +
				" \"Date_Start\" DATETIME DEFAULT CURRENT_DATE, \"Date_End\" " +
				"DATETIME DEFAULT CURRENT_DATE)" +
				"");
		this.execSQL("" +
				"CREATE VIEW \"V_Course\" AS select c.C_ID, cl.CL_ID, a.A_ID"+
				" from Course c, Class cl, Assignment a, Contact_Assignment ca"+
				" where c.C_ID=cl.C_ID"+
				" and cl.CL_ID=a.CL_ID"+
				" and a.A_ID=ca.A_ID" +
				"");
		this.execSQL("" +
				"CREATE VIEW \"V_Semester\" AS select s.S_ID, c.C_ID, cl.CL_ID, a.A_ID"+
				" from Semester s, Course c, Class cl, Assignment a, Contact_Assignment ca"+
				" where s.S_ID=c.S_ID "+
				" and c.C_ID=cl.C_ID "+
				" and cl.CL_ID=a.CL_ID "+
				" and a.A_ID=ca.A_ID" +
				"");
		this.execSQL("" +
				"CREATE TRIGGER _fk_Assignment_delete "+
				" after delete on \"Assignment\" for each row "+
				" begin "+
				" delete from \"Contact_Assignment\" where \"A_ID\"=old.\"A_ID\"; "+
				" end" +
				"");
		this.execSQL("" +
				"CREATE TRIGGER _fk_Assignment_insert_0 BEFORE INSERT" +
				" ON \"Assignment\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
				" 'insert on table Assignment violates foreign key constraint') " +
				" WHERE  (SELECT \"CL_ID\" FROM \"Class\" WHERE \"CL_ID\" = NEW.\"CL_ID\") IS NULL; END" +
				"");
		this.execSQL("" +
				"CREATE TRIGGER _fk_Assignment_update_0 BEFORE UPDATE ON \"Assignment\" " +
				" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table Assignment violates" +
				" foreign key constraint') WHERE  (SELECT \"CL_ID\" FROM \"Class\" WHERE \"CL_ID\" =" +
				"  NEW.\"CL_ID\") IS NULL; END" +
				"");
		this.execSQL("" +
				"CREATE TRIGGER _fk_Contact_Assignment_insert_0 BEFORE INSERT ON " +
				" \"Contact_Assignment\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
				" 'insert on table Contact_Assignment violates foreign key constraint')" +
				"  WHERE  (SELECT \"CT_ID\" FROM \"Contact\" WHERE \"CT_ID\" = NEW.\"CT_ID\") IS NULL; END" +
				"");
		
		this.execSQL("" +
				"CREATE TRIGGER _fk_Contact_Assignment_insert_1 BEFORE INSERT ON" +
				" \"Contact_Assignment\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
				"'insert on table Contact_Assignment violates foreign key constraint') " +
				"WHERE  (SELECT \"A_ID\" FROM \"Assignment\" WHERE \"A_ID\" = NEW.\"A_ID\") IS NULL; END" +
				"");
		this.execSQL("" +
				"CREATE TRIGGER _fk_Contact_Assignment_update_0 BEFORE UPDATE ON " +
				" \"Contact_Assignment\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
				" 'update on table Contact_Assignment violates foreign key constraint') " +
				" WHERE  (SELECT \"CT_ID\" FROM \"Contact\" WHERE \"CT_ID\" = NEW.\"CT_ID\") IS NULL; END" +
				"");
		this.execSQL("" +
				"CREATE TRIGGER _fk_Contact_Assignment_update_1 BEFORE UPDATE ON" +
				"  \"Contact_Assignment\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
				" 'update on table Contact_Assignment violates foreign key constraint') " +
				" WHERE  (SELECT \"A_ID\" FROM \"Assignment\" WHERE \"A_ID\" = NEW.\"A_ID\") IS NULL; END" +
				"");
		this.execSQL("" +
				"CREATE TRIGGER _fk_Contact_delete"+
				" after delete on \"Contact\" for each row "+
				" begin "+
				" delete from \"Contact_Assignment\" where \"CT_ID\"=old.\"CT_ID\";"+
				" update \"Class\" set \"CT_ID\"=NULL where \"CT_ID\"=old.\"CT_ID\";  "+
				" end" +
				"");
		this.execSQL("" +
				"CREATE TRIGGER _fk_Course_delete"+
				" after delete on \"Course\" for each row"+
				" begin"+
				" delete from \"Contact_Assignment\" where \"A_ID\" in "+
				" (select \"A_ID\" from V_Course where \"C_ID\"=old.\"C_ID\");"+
				" delete from \"Assignment\" where \"CL_ID\" in (select CL_ID from \"Class\" where \"C_ID\"=old.\"C_ID\");"+
				" delete from \"Class\" where \"C_ID\"=old.\"C_ID\";"+
				" delete from \"Exam\" where \"C_ID\"=old.\"C_ID\";"+
				" end" +
				"");
		this.execSQL("" +
				"CREATE TRIGGER _fk_Semester_delete"+
				" after delete on \"Semester\" for each row"+
				" begin"+
				" delete from \"Contact_Assignment\" where \"A_ID\" in "+
				" (select \"A_ID\" from V_Semester where \"S_ID\"=old.\"S_ID\");"+
				" delete from \"Assignment\" where \"CL_ID\" in "+
				" (select \"CL_ID\" from V_Semester where \"S_ID\"=old.\"S_ID\");"+
				" delete from \"Class\" where \"C_ID\" in  (select \"C_ID\" from V_Semester where \"S_ID\"=old.\"S_ID\"); "+
				" delete from \"Exam\" where \"C_ID\" in  (select \"C_ID\" from V_Semester where \"S_ID\"=old.\"S_ID\"); "+
				" delete from \"Course\" where \"S_ID\"=old.\"S_ID\"; "+
				"end" +
				"");
		this.execSQL("" +
				"CREATE TRIGGER _fk_Course_insert_0 BEFORE INSERT ON " +
				" \"Course\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
				" 'insert on table Course violates foreign key constraint')" +
				" WHERE  (SELECT \"S_ID\" FROM \"Semester\" WHERE \"S_ID\" = NEW.\"S_ID\") IS NULL; END" +
				"");
		this.execSQL("" +
				"CREATE TRIGGER _fk_Course_update_0 BEFORE UPDATE ON" +
				" \"Course\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK," +
				" 'update on table Course violates foreign key constraint') WHERE " +
				" (SELECT \"S_ID\" FROM \"Semester\" WHERE \"S_ID\" = NEW.\"S_ID\") IS NULL; END" +
				"");
		this.execSQL("" +
				"CREATE TRIGGER _fk_Exam_insert_0 BEFORE INSERT ON \"Exam\" " +
				" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK," +
				" 'insert on table Exam violates foreign key constraint') WHERE " +
				" (SELECT \"C_ID\" FROM \"Course\" WHERE \"C_ID\" = NEW.\"C_ID\") IS NULL; END" +
				"");
		this.execSQL("" +
				"CREATE TRIGGER _fk_Exam_insert_1 BEFORE INSERT ON" +
				" \"Exam\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK," +
				" 'insert on table Exam violates foreign key constraint')" +
				" WHERE  NEW.\"CL_ID\" IS NOT NULL AND  (SELECT \"CL_ID\" FROM \"Class\"" +
				" WHERE \"CL_ID\" = NEW.\"CL_ID\") IS NULL; END" +
				"");
		this.execSQL("" +
				"CREATE TRIGGER _fk_Exam_update_0 BEFORE UPDATE ON" +
				" \"Exam\" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
				" 'update on table Exam violates foreign key constraint') WHERE " +
				" (SELECT \"C_ID\" FROM \"Course\" WHERE \"C_ID\" = NEW.\"C_ID\") IS NULL; END" +
				"");
		this.execSQL("" +
				"CREATE TRIGGER _fk_Exam_update_1 BEFORE UPDATE ON \"Exam\" " +
				" FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, " +
				"'update on table Exam violates foreign key constraint')" +
				" WHERE  NEW.\"CL_ID\" IS NOT NULL AND  (SELECT \"CL_ID\" FROM \"Class\" " +
				" WHERE \"CL_ID\" = NEW.\"CL_ID\") IS NULL; END" +
				"");
	}
}
